package com.scalar.chiptrack.adaptor.beceem;

import java.io.File;
import java.util.ArrayList;
import java.util.Date;
import java.util.Map;
import java.util.HashMap;

import com.scalar.ScalarException;
import com.scalar.chiptrack.adaptor.AdaptorConstants;
import com.scalar.chiptrack.adaptor.AdaptorUtils;
import com.scalar.chiptrack.adaptor.utils.Parser;
import com.scalar.chiptrack.adaptor.utils.ParserFactory;
import com.scalar.chiptrack.adaptor.utils.ParserResult;
import com.scalar.chiptrack.adaptor.utils.XLSParserImpl;
import com.scalar.chiptrack.commons.Address;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DBXException;
import com.scalar.chiptrack.sales.parser.SalesParserDAO;
import com.scalar.chiptrack.sales.parser.SalesParserLineItem;
import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.utils.StringUtils;

public class SalesParser {
    private Logger m_parserLogger = null;

    public SalesParser() throws Exception {
        try {
            m_parserLogger = new Logger("./logs", "salesPorting", "Parser", "debug", 1024 * 1000, 5 );
            //m_parserLogger = AdaptorUtils.getDataParserLogger("", "salesParser");
            AdaptorUtils.setLogger(m_parserLogger);
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }

    // To be implemented by sub classes
    public void execute() throws Exception {
        boolean success = false;
        AdaptorUtils.printHeader("SALES PORTING", "PARSER");
        File backlogFile = getFileForPorting(System.getProperty("salesOrderFile"));
        log("Parsing File:" + backlogFile.getName());
        System.out.println("Parsing Sales Order File:" + backlogFile.getName());

        log("Parsing Sales Order sheet....");
        System.out.println("Parsing Sales Order sheet....");

        ArrayList<SalesParserLineItem> lineItemList = parseSalesOrdersXLSFile(backlogFile);

        log("Parsing Sales backlog completed...");
        System.out.println("Parsing Sales backlog completed...");
        log("Parsing completed successfully...");
        System.out.println("Parsing completed successfully.....");
        log("Porting Sales Backlog info...");
        System.out.println("Porting Sales Backlog info...");

        DBTransaction dbTransaction = null;
        String portingStatusMsg = null;
        try {
            dbTransaction = DBTransaction.getInstance();
            dbTransaction.begin();
            SalesParserDAO.saveSalesOrdersBeceem(dbTransaction, lineItemList);
            if (dbTransaction != null) {
                dbTransaction.commit();
            }
            success = true;
            int listSize = lineItemList.size();
            for (int i = 0; i < listSize; i++) {
                SalesParserLineItem lineItem = (SalesParserLineItem) lineItemList.get(i);
                switch (lineItem.getPortingStatusCode()) {
                    case 0: //'SUCCESS' NEW LINE ITEM CREATED
                        break;
                    case 1: //'SUCCESS' LINE ITEM UPDATED
                        break;
                    case 2: //'FAILED' SALES ORDER NUMBER REQUIRED
                        log("***NOT PORTED*** SALES ORDER NUMBER REQUIRED. SALES ORDER NO: " + lineItem.getSalesOrderNo() + " lineItemNo:" + lineItem.getLineItemNo() + " shipmentNo:" + lineItem.getShipmentNo());
                        break;
                    case 3: //'FAILED' NO FG PART NUMBER FOUND
                        log("***NOT PORTED*** MFG PART NUMBER NOT FOUND. MFG PART NUMBER: " + lineItem.getManufacturingPartNo() + " SALES ORDER NO: " + lineItem.getSalesOrderNo() + " lineItemNo:" + lineItem.getLineItemNo() + " shipmentNo:" + lineItem.getShipmentNo());
                        break;
                    case 4: //'FAILED' CUSTOMER NOT FOUND
                        log("***NOT PORTED*** CUSTOMER NOT FOUND. CUSTOMER NUMBER: " + lineItem.getCustomerNumber() + " SALES ORDER NO: " + lineItem.getSalesOrderNo() + " lineItemNo:" + lineItem.getLineItemNo() + " shipmentNo:" + lineItem.getShipmentNo());
                        break;
                    case 5: //'FAILED' SHIPPING ADDRESS NOT FOUND
                        log("***NOT PORTED*** SHIPPING ADDRESS NOT FOUND. SHIP TO TAG NAME: " + lineItem.getShipToTagName() + " SALES ORDER NO: " + lineItem.getSalesOrderNo() + " lineItemNo:" + lineItem.getLineItemNo() + " shipmentNo:" + lineItem.getShipmentNo());
                        break;
                }
            }

        }
        catch (Exception e) {
            log("Exception while porting Sales Orders");
            m_parserLogger.logDebug(e.getMessage(), e);
            throw e;
        }
        finally {
            if (dbTransaction != null) {
                if (!success) {
                    try {
                        dbTransaction.rollback();
                    } catch (DBXException ignore) {
                    }
                }
                dbTransaction = null;
            }
        }
        System.out.println("********SEE LOG FILE FOR NOT PORTED LINE ITEMS********");
        log("Porting Sales Backlog completed successfully....");
        System.out.println("Porting Sales Backlog completed successfully....");
    }

    private void log(String message) {
        if (message == null) return;

        if (m_parserLogger != null) {
            m_parserLogger.logDebug(message + AdaptorConstants.LINE_SEPARATOR);
        } else {
            System.out.println(message);
        }
    }

    private File getFileForPorting(String salesPortingFile) throws ScalarException {
        if (salesPortingFile == null || salesPortingFile.trim().length() == 0) {
            log("Sales Porting File Name not specified");
            System.out.println("Sales Porting File Name not specified");
            throw new ScalarException("Sales Porting File Name not specified");
        }
        File portingFile = new File(salesPortingFile);
        if (portingFile.exists() == false && portingFile.isFile() == false) {
            log("Sales Porting File:" + salesPortingFile + " Does not exist");
            throw new ScalarException(salesPortingFile + " does not exist");
        } else {
            log("Sales Porting File:" + salesPortingFile + " Found");
            System.out.println("Sales Porting File:" + salesPortingFile + " Found");
        }
        return portingFile;
    }


    public static void main(String[] args) throws Exception {
        SalesParser salesParser = new SalesParser();
        salesParser.execute();
    }

    private ArrayList<SalesParserLineItem> parseSalesOrdersXLSFile(File salesPortingFile) throws Exception 
    {
		Map<String, SalesParserLineItem>  liNoCheckMap = new HashMap<String, SalesParserLineItem>();
        SalesParserLineItem lineItem = null;
        ArrayList<SalesParserLineItem> lineItemList = new ArrayList<SalesParserLineItem>();
        Address shipaddress = null;
        
        ParserResult result = null;
        Parser parser = ParserFactory.getXLSParser(salesPortingFile);

        ((XLSParserImpl) parser).setHeaderRowIdx(0);
        ((XLSParserImpl) parser).setDataStartRowIdx(1);

        result = parser.parse();
        while (result.next()) {
            try {
                lineItem = new SalesParserLineItem();
                lineItem.setSalesOrderNo( result.getString( "Document Number" ) );
                if( lineItem.getSalesOrderNo() != null && lineItem.getSalesOrderNo().startsWith( "BK" ) )
                {
                	lineItem.setSalesOrderNo( result.getString( "Original Number" ) );	
                }
                lineItem.setCustomerNumber( result.getString( "Customer Number" ) );
                lineItem.setCustomerName( result.getString( "Customer Name" ) );
                lineItem.setCustomerPO( result.getString( "Customer PO Number" ) );
                
                if( result.getString( "Document Number" ) != null && result.getString( "Document Number" ).startsWith( "BK" ) )
                {
                	lineItem.setBookingDate( result.getString("Back Order Date") );
                }
                else
                {
                	lineItem.setBookingDate( result.getString("Order Date") );
                }
				if("#NUM!".equalsIgnoreCase(lineItem.getBookingDate())) {
					lineItem.setBookingDate(null);
				} else {
					//need to conver mm/dd/yy to mm/dd/yyyy
					Date bookingDate = StringUtils.format(lineItem.getBookingDate(), "MM/dd/yy");
					String bookingDateStr = StringUtils.formatDateToString(bookingDate);
					lineItem.setBookingDate(bookingDateStr);
				}

                lineItem.setManufacturingPartNo( result.getString("Item Number") );
                if( result.getString( "Document Number" ) != null && result.getString( "Document Number" ).startsWith( "BK" ) )
                {
                	lineItem.setQuantity( result.getInt("QTY") );
                }
                else
                {
                	lineItem.setQuantity( result.getInt("QTY To Back Order") );
                }
                if(lineItem.getQuantity() == 0) {
					log("***NOT PORTED*** QTY is ZERO lineitem: SO NO: " + lineItem.getSalesOrderNo() + " MFG Part NO: " + lineItem.getManufacturingPartNo());
				}
                lineItem.setRequestedDockDate( result.getString( "Requested Ship Date" ) );
				if("#NUM!".equalsIgnoreCase(lineItem.getRequestedDockDate())) {
					lineItem.setRequestedDockDate(null);
				} else {
					//need to conver mm/dd/yy to mm/dd/yyyy
					Date reqDockDate = StringUtils.format(lineItem.getRequestedDockDate(), "MM/dd/yy");
					String reqDockDateStr = StringUtils.formatDateToString(reqDockDate);
					lineItem.setRequestedDockDate(reqDockDateStr);
				}
                lineItem.setUnitPrice( new Float( result.getDouble( "Unit Price" ) ) );
                
                shipaddress = new Address();
                shipaddress.setAddress1( lineItem.getCustomerName() );
                shipaddress.setAddress2( result.getString( "Address 1 from Sales Line Item" ) );
                shipaddress.setAddress3( result.getString( "Address 2 from Sales Line Item" ) );
                shipaddress.setCity( result.getString( "City from Sales Line Item" ) );
                shipaddress.setState( result.getString( "State from Sales Line Item" ) );
                shipaddress.setCountry( result.getString( "Country from Sales Line Item" ) );
                shipaddress.setZipCode( result.getString( "Zip Code from Sales Line Item" ) );
                lineItem.setLineItemNo( result.getInt( "Line Item Sequence" ) );
                lineItem.setShipAddress( shipaddress );
                
                /*lineItem.setBookingDate(StringUtils.formatDateToString(result.getDate("Order Entry Date")));
                lineItem.setSalesOrderNo(result.getString("SO NUMBER"));
                if (lineItem.getSalesOrderNo() == null || lineItem.getSalesOrderNo().trim().length() == 0) {
                    log("**NOT PORTED** Sales Order No Missing for the row:" + (result.getFileRowIdx()+1));
                    continue;
                }

                lineItem.setCustomerPO(result.getString("CUSTOMER PO NUMBER"));
                lineItem.setInternalPOLineNo( result.getInt( "PO Line Item No" ) );
                lineItem.setLineItemNo(result.getInt("LINE ITEM"));
                lineItem.setCustomerNumber(result.getString("BILL-TO\n" +
                        "CUST CODE"));
                lineItem.setCustomerName(result.getString("BILL-TO CUSTOMER NAME"));
                if (lineItem.getCustomerNumber() == null || lineItem.getCustomerNumber().trim().length() == 0) {
                    log("**NOT PORTED** Bill to Cust Code Missing for the row:" + (result.getFileRowIdx()+1));
                    continue;
                }
                lineItem.setPaymentTerms(result.getString("PAYMENT TERMS"));
                lineItem.setShipToTagName(result.getString("SHIP-TO\n" +
                        "CUST CODE"));
                lineItem.setEndCustomer(result.getString("END CUSTOMER NAME"));
                lineItem.setManufacturingPartNo(result.getString("AKROS ORDERABLE PART NUMBER"));
                lineItem.setCustomerPartNo(result.getString("CUSTOMER'S PART NUMBER"));
                lineItem.setRegion(result.getString("BILL TO GEO"));
                try{
                    lineItem.setQuantity(result.getInt("ORDER QTY "));
                } catch(NumberFormatException e) {
                    String qtyStr = result.getString("ORDER QTY ");
                    qtyStr = qtyStr.replaceAll("[\\$\\,\\\"]","");
                    lineItem.setQuantity(Integer.parseInt(qtyStr));
                }

                String priceStr = result.getString("UNIT PRICE");
                priceStr = priceStr.replaceAll("\\$","");
                priceStr = priceStr.replaceAll("\\,","");
                priceStr = priceStr.replaceAll("[\"]","");
                lineItem.setUnitPrice(Float.parseFloat(priceStr));
                //lineItem.setUnitPrice((float) result.getDouble("UNIT PRICE"));
                //lineItem.setTotalAmt(result.getDouble("Ext. Price"));
                lineItem.setRequestedDockDate(StringUtils.formatDateToString(result.getDate("CUSTOMER REQUEST DATE")));
                if (lineItem.getRequestedDockDate() == null || lineItem.getRequestedDockDate().trim().length() == 0) {
                    log("**NOT PORTED** SalesOrder Requested Dock Date Missing for the row:" + (result.getFileRowIdx()+1));
                }
                lineItem.setScheduleDate(StringUtils.formatDateToString(result.getDate("ORIGINAL SCHEDULED SHIP DATE")));
                lineItem.setRevisedScheduleDate(StringUtils.formatDateToString(result.getDate("CURRENT SCHEDULED SHIP DATE")));
                lineItem.setDescription(result.getString("NOTES and/or SPECIAL INSTRUCTIONS"));
                lineItem.setStatus(Constants.SALESORDER_STATUS_APPROVED);
                if( "Sample".equalsIgnoreCase( result.getString("CUSTOMER PO NUMBER") ) )
                {
                	lineItem.setIsSample(0);
                }
                else
                {
                	lineItem.setIsSample(1);	
                }
                
                lineItem.setShippedDate(StringUtils.formatDateToString(result.getDate("ACTUAL SHIP DATE")));
                lineItem.setShippingLocation("SPEL".equalsIgnoreCase(result.getString("SHIPPED FROM")) ? "SPEL" : "Akros Silicon - Folsom" );
                */

				String  lineNoKey = lineItem.getSalesOrderNo() + ":" + lineItem.getLineItemNo();
				if(liNoCheckMap.containsKey(lineNoKey)) {
					log("**NOT PORTED** Line Item No already exists. SO No:" + lineItem.getSalesOrderNo() + " LI NO:" + lineItem.getLineItemNo() + " MFG NO:" + lineItem.getManufacturingPartNo());
				} else {
					liNoCheckMap.put(lineNoKey, lineItem);
					lineItemList.add(lineItem);
				}

            } catch (Exception e) {
                log("**NOT PORTED** Exception while parsing the line item row:" + (result.getFileRowIdx()+1));
                m_parserLogger.logDebug(e.getMessage(), e);
            }
        }
        return lineItemList;
    }
}

